package dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import connection.JDCConnection;

import model.Cours;
import model.Seance;

public class SessionCouranteDAO {
	
	public ArrayList<Seance> getSeanceListByEtudiantId(JDCConnection connection,int idEtudiant) {
		Statement stmt;
		ResultSet rs;
		ArrayList<Seance> list =new ArrayList<Seance>();
		
		try {

			stmt = connection.createStatement();
			rs = stmt.executeQuery("SELECT idSeance, debut, fin, type, Groupe.idGroupe, jour, sigle, noGroupe FROM SessionCourante, Groupe, Cours, Seance WHERE SessionCourante.idEtudiant = "+idEtudiant+" AND SessionCourante.idGroupe = Groupe.idGroupe AND Groupe.idCours = Cours.idCours AND Seance.idGroupe = Groupe.idGroupe");
			while (rs.next()) {
				Seance seance = new Seance();

				seance.setIdSeance(rs.getInt("idSeance"));
				seance.setDebut(rs.getInt("debut"));
				seance.setFin(rs.getInt("fin"));
				seance.setType(rs.getInt("type"));
				seance.setIdGroupe(rs.getInt("idGroupe"));
				seance.setJour(rs.getInt("jour"));
				seance.setDuree(seance.getFin() - seance.getDebut());
				seance.setSigle(rs.getString("sigle"));
				seance.setNoGroupe(rs.getInt("noGroupe"));
				
				//ajout a la liste
				list.add(seance);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return list;
	}
	public ArrayList<Cours> getCoursSessionCourante(JDCConnection connection,int idEtudiant) {
		Statement stmt;
		ResultSet rs;
		ArrayList<Cours> list =new ArrayList<Cours>();
		
		try {

			stmt = connection.createStatement();
			  //System.out.println("SELECT Cours.idCours,sigle,disponible,preRequis,nbCredit,description FROM Cours,SessionCourante,Groupe where idEtudiant="+idEtudiant+" and Groupe.idGroupe=SessionCourante.idGroupe and Cours.idCours=Groupe.idCours");
			rs = stmt.executeQuery("SELECT Cours.idCours,sigle,disponible,preRequis,nbCredit,description FROM Cours,SessionCourante,Groupe where idEtudiant="+idEtudiant+" and Groupe.idGroupe=SessionCourante.idGroupe and Cours.idCours=Groupe.idCours");
			while (rs.next()) {
				Cours cours = new Cours();

				cours.setIdCours(rs.getInt("idCours"));
				cours.setSigle(rs.getString("sigle"));
				cours.setDisponible(rs.getBoolean("disponible"));
				cours.setPreRequis(rs.getInt("preRequis"));
				cours.setNbCredit(rs.getInt("nbCredit"));
				cours.setDescription(rs.getString("description"));
				
				//ajout a la liste
				list.add(cours);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return list;
	}
	

	
	public boolean addGroupe(JDCConnection connection,int idGroupe, int idEtudiant) {
		Statement stmt;
		try {

			stmt = connection.createStatement();
			stmt.executeUpdate("INSERT INTO SessionCourante(idEtudiant,idGroupe)" + " VALUES ('"+ 
					idEtudiant+"','"+ 
					idGroupe+"')");
			
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return false;
			}
			
		return true;
	}
	
	public boolean removeGroupe(JDCConnection connection,int idGroupe, int idEtudiant) {
		Statement stmt;
		try {

			stmt = connection.createStatement();
			stmt.executeUpdate("DELETE From SessionCourante where idEtudiant="+idEtudiant+" AND idGroupe="+idGroupe);   
						
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return false;
			}
			return true;
	}
	
	public int getNbCreditInscrit(JDCConnection connection, int idEtudiant) {
		Statement stmt;
		ResultSet rs;
		int sommeCredit=0;
		try {
			stmt = connection.createStatement();	
			rs = stmt.executeQuery("Select  sum(nbCredit) from Groupe,SessionCourante,Cours where Groupe.idGroupe=SessionCourante.idGroupe and idEtudiant="+idEtudiant+" and Cours.idCours=Groupe.idCours");
			while (rs.next()) {
				sommeCredit=rs.getInt(1);
			}
		
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return sommeCredit;
	}
	
}
